package commons;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;


public abstract class BaseDBUtils {
	//创建实例化对象用于将线程uiConnection绑定
	private static ThreadLocal<Connection> t =  new ThreadLocal<Connection>();
	//获取数据源
	 private static DataSource ds = new  ComboPooledDataSource();
	 /**
	  *获取数据源
	  *
	  *@return 数据源 
	  */
	 public static DataSource getDataSource() {
		 return ds;
	 }
	 /**
	  *获取Connection
	  * 
	  * @return 获取数据库连接Connection
	  * @Throws SQLException SQL异常
	  * 
	  */
	 public static Connection getConnection() throws SQLException {
		 Connection conn = t.get();
		 if (conn!=null) {
			//如果若获取到的值不为null则将获取到的连接直接返回
			 return conn;
		}
		conn = ds.getConnection();
		//如果若获取到的值为null则将通过驱动刮玻璃起获取连接
		t.set(conn);
		//将获取到的连接与线程绑定
		return conn;
		//将连接返回
	 }
	 /**
		 * 开启事务
		 * 
		 * @throws SQLException SQLException
		 */
	 public static void startTransaction() throws SQLException {
		 getConnection().setAutoCommit(false);
		 //关闭当前连接的自动提交功能
	 }
	 /**
		 * 获取PreparedStatement
		 * 
		 * @param conn Connection
		 * @param sql  SQl语句
		 * @return PreparedStatement
		 * @throws SQLException SQl异常
		 */
	 public static PreparedStatement getPreparedStatement(Connection conn, String sql) throws SQLException {
			System.out.println(sql);
			return conn.prepareStatement(sql);
		}
	 /**
		 * 获取PreparedStatement
		 * 
		 * @param conn Connection
		 * @param sql  SQl语句
		 * @return PreparedStatement
		 * @throws SQLException SQl异常
		 */
		public static PreparedStatement getPreparedStatement(Connection conn, String sql, PageRang pr) throws SQLException {
			// MySQL分页中每页的起始index
			// int index = (pr.getPageNumber() - 1) * pr.getPageSize();
			// sql += " limit " + index + " , " + pr.getPageSize();
			// sql += " limit " + (pr.getPageNumber() - 1) * pr.getPageSize() + " , " +
			// pr.getPageSize();
			sql += " limit " + pr.getPageIndex() + " , " + pr.getPageSize();
			System.out.println(sql);
			// 在该连接中预编译SQL
			return conn.prepareStatement(sql);
		}

		/**
		 * 
		 * @param pst   PreparedStatement
		 * @param param 查询参数
		 * @return ResultSet
		 * @throws SQLException SQl异常
		 */
		public static ResultSet executeQuery(PreparedStatement pst, Object... params) throws SQLException {
			if (params != null && params.length > 0) {
				// 如果参数数组不是null并且长度大于零
				for (int i = 0; i < params.length; i++) {
					// 遍历数组元素的值并将该值设置到PreparedStatement对象中
					pst.setObject(i + 1, params[i]);
				}
			}
			return pst.executeQuery();
		}

		/**
		 * 
		 * @param pst   PreparedStatement
		 * @param param 查询参数
		 * @return ResultSet
		 * @throws SQLException SQl异常
		 */
		public static ResultSet executeQuery(PreparedStatement pst, PageRang pr, Object... params) throws SQLException {
			if (params != null && params.length > 0) {
				// 如果参数数组不是null并且长度大于零
				for (int i = 0; i < params.length; i++) {
					// 遍历数组元素的值并将该值设置到PreparedStatement对象中
					pst.setObject(i + 1, params[i]);
				}
			}
			// 设置最大行数
			pst.setMaxRows(pr.getPageIndex() + pr.getPageSize());
			// 获取结果期
			ResultSet rs = pst.executeQuery();
			// 相对移动
			rs.relative(pr.getPageIndex());
			return rs;
		}

		public static int executeUpdate(PreparedStatement pst, Object... params) throws SQLException {
			if (params != null && params.length > 0) {
				// 如果参数数组不是null并且长度大于零
				for (int i = 0; i < params.length; i++) {
					// 遍历数组元素的值并将该值设置到PreparedStatement对象中
					pst.setObject(i + 1, params[i]);
				}
			}
			return pst.executeUpdate();
		}

		public static void closeAll(Connection conn, PreparedStatement pst, ResultSet rs) {
			// 关闭连接
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		public static void closeAll(PreparedStatement pst, ResultSet rs) {
			// 关闭连接
			try {
				if (rs != null) {
					rs.close();
				}
				if (pst != null) {
					pst.close();
				}
				Connection conn = getConnection();
				if (conn != null) {
					conn.close();
				}
				// 从当前线程中移除Connection
				t.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		public static void commitAndClose(PreparedStatement pst) {
			// 关闭连接
			try {
				// 获取当前线程连接
				Connection conn = getConnection();
				// 手动提交事务
				conn.commit();
				if (pst != null) {
					pst.close();
				}
				if (conn != null) {
					conn.close();
				}
				// 从当前线程中移除Connection
				t.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		public static void roolbackAndClose(PreparedStatement pst) {
			// 关闭连接
			try {
				// 获取当前线程连接
				Connection conn = getConnection();
				// 事务回滚
				conn.rollback();
				if (pst != null) {
					pst.close();
				}
				if (conn != null) {
					conn.close();
				}
				// 从当前线程中移除Connection
				t.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	 
}
